package com.btpn.custody.dao.hibernate;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.CacheMode;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.btpn.custody.common.Constant;
import com.btpn.custody.dao.IAccount;
import com.btpn.custody.entity.Account;
import com.btpn.custody.web.m003.model.Report03Model;
import com.btpn.infra.utils.MyDateUtils;

@Repository("accountDaoHibernate")
public class AccountDAO implements IAccount {

	protected HibernateTemplate template = null;

	protected Session session = null;

	@Autowired
	@Required
	public void setSessionFactory(SessionFactory sessionFactory) {
		template = new HibernateTemplate(sessionFactory);
		session = sessionFactory.openSession();
	}

	@Override
	public Account get(Long id) {
		return template.get(Account.class, id);
	}

	@Override
	public List<Account> findAll() {
		return template.loadAll(Account.class);
	}

	@Override
	public Integer getCount() {
		return Integer.valueOf(findAll().size());
	}

	@Override
	public Serializable create(Account account) throws DataAccessException {
		return template.save(account);
	}

	@Override
	public Account update(Account account) {
		template.update(account);

		return account;
	}

	@Override
	public void createOrUpdate(Account account) {
		template.saveOrUpdate(account);
	}

	@Override
	public void delete(Long id) {
		template.delete(get(id));
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Account getByCifAccountSufix(String cif, String account, String sufix) {
		List<Account> result = template.findByNamedQueryAndNamedParam(
				Account.NQ__GET_UNIQUE_ACCOUNT, new String[] { "cif",
						"account", "sufix" }, new Object[] { cif, account,
						sufix });

		if (result.size() > 0) {
			return result.get(0);
		} else {
			return null;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Account> findAllByDateRange(Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(
				Account.NQ__FIND_BY_DATE_RANGE, new String[] { "from", "to" },
				new Object[] { from, to });
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Account> findByAreaDateRange(Long areaId, Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(
				Account.NQ__FIND_BY_AREA_AND_DATE_RANGE, new String[] {
						"areaId", "from", "to" }, new Object[] { areaId, from,
						to });
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Account> findByBranchDateRange(Long branchId, Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(
				Account.NQ__FIND_BY_BRANCH_AND_DATE_RANGE, new String[] {
						"branchId", "from", "to" }, new Object[] { branchId,
						from, to });
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Account> findAllCloseByDateRange(Date from, Date to) {
		return template.findByNamedQueryAndNamedParam(
				Account.NQ__FIND_CLOSE_BY_DATE_RANGE, new String[] { "from",
						"to" }, new Object[] { from, to });
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Account> findCloseByAreaDateRange(Long areaId, Date from,
			Date to) {
		return template.findByNamedQueryAndNamedParam(
				Account.NQ__FIND_CLOSE_BY_AREA_AND_DATE_RANGE, new String[] {
						"areaId", "from", "to" }, new Object[] { areaId, from,
						to });
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Account> findCloseByBranchDateRange(Long branchId, Date from,
			Date to) {
		return template.findByNamedQueryAndNamedParam(
				Account.NQ__FIND_CLOSE_BY_BRANCH_AND_DATE_RANGE, new String[] {
						"branchId", "from", "to" }, new Object[] { branchId,
						from, to });
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Long countForAccuracyMasterSLA(Long areaId, String status,
			Integer month, Integer year) {
		List<Long> list = template.findByNamedQueryAndNamedParam(
				Account.NQ__REPORT_02_MASTER_BY_AREA, new String[] { "areaId",
						"status", "month", "year" }, new Object[] { areaId,
						status, month, year });

		return list.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Long countForAccuracyDetailSLA(Long branchId, String status,
			Integer month, Integer year) {
		List<Long> list = template.findByNamedQueryAndNamedParam(
				Account.NQ__REPORT_02_DETAIL_BY_BRANCH, new String[] {
						"branchId", "status", "month", "year" }, new Object[] {
						branchId, status, month, year });

		return list.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Long countForAccuracyAllSLA(String status, Integer month,
			Integer year) {
		List<Long> list = template.findByNamedQueryAndNamedParam(
				Account.NQ__REPORT_02_ALL, new String[] { "status", "month",
						"year" }, new Object[] { status, month, year });

		return list.get(0);
	}

	@Override
	public List<Report03Model> findByAreaDateRangeByModel(Long areaId,
			String from, String to) {
		List<Report03Model> rows = null;

		String q = "select  branch2_.BRANCH_CODE as branchCode,"
				+
				// "branch2_.BRANCH_NAME as branchName,"+
				"account0_.ACCOUNT as account,"
				+ "masterdata1_.CIF_SID as cif,"
				+ "account0_.ACCOUNT_OPEN as accOpenDate,"
				+ "account0_.CREATED_USER_ID as accDamaro,"
				+ "account0_.ACCOUNT_CLOSE as ACCOUNT_CLOSE,"
				+ "masterdata1_.APPROVED_USER as approvalUser,"
				+ "masterdata1_.APPROVED_DATE as approvalDate,"
				+ "masterdata1_.WAIT_APPROVAL_USER as lastCheckUser,"
				+ "masterdata1_.WAIT_APPROVAL_DATE as waitAppDate,"
				+ "masterdata1_.RECEIVED_USER as receivedUser,"
				+ "masterdata1_.RECEIVED_DATE as receivedDate,"
				+ "masterdata1_.TBO_USER as tboUser,"
				+ "masterdata1_.TBO_DATE as tboDate,"
				+ "masterdata1_.PROGRESS_USER as progressUser,"
				+ "masterdata1_.PROGRESS_DATE as progressDate,"
				+ "masterdata1_.SEND_EQ_DATE as sendToEQdate "
				+

				"from M_ACCOUNT account0_ left join T_CIF masterdata1_ on account0_.MD_ID=masterdata1_.__SID "
				+ "left join M_BRANCH branch2_ ON account0_.BRANCH=branch2_.BRANCH_CODE "
				+ "where account0_.MD_ID=masterdata1_.__SID and masterdata1_.BRANCH_ID=branch2_.__ID "
				+ "and branch2_.AREA_ID='"
				+ areaId
				+ "' "
				+ "and CONVERT(VARCHAR(20),account0_.ACCOUNT_OPEN, 112) BETWEEN '"
				+ from + "' AND '" + to + "' "
				+ "order by branch2_.BRANCH_CODE";
		SQLQuery query = session.createSQLQuery(q);
		query.addScalar("branchCode", Hibernate.STRING)
				// .addScalar("branchName", Hibernate.STRING)
				.addScalar("account", Hibernate.STRING)
				.addScalar("cif", Hibernate.STRING)
				.addScalar("accOpenDate", Hibernate.DATE)
				.addScalar("accDamaro", Hibernate.STRING)
				.addScalar("approvalUser", Hibernate.STRING)
				.addScalar("approvalDate", Hibernate.DATE)
				.addScalar("lastCheckUser", Hibernate.STRING)
				.addScalar("waitAppDate", Hibernate.DATE)
				.addScalar("receivedUser", Hibernate.STRING)
				.addScalar("receivedDate", Hibernate.DATE)
				.addScalar("tboUser", Hibernate.STRING)
				.addScalar("tboDate", Hibernate.DATE)
				.addScalar("progressUser", Hibernate.STRING)
				.addScalar("progressDate", Hibernate.DATE)
				.addScalar("sendToEQdate", Hibernate.DATE)
				.setResultTransformer(
						Transformers.aliasToBean(Report03Model.class))
				.setCacheMode(CacheMode.GET).list();
		rows = query.list();
		System.out.println(q);
		System.out.println("list count: " + rows.size());
		return rows;
	}

	@Override
	public List<Report03Model> findByBranchDateRangeByModel(Long branchId,
			String from, String to) {
		List<Report03Model> rows = null;
		String q = "select  branch2_.BRANCH_CODE as branchCode,"
				+
				// "branch2_.BRANCH_NAME as branchName,"+
				"account0_.ACCOUNT as account,"
				+ "masterdata1_.CIF_SID as cif,"
				+ "account0_.ACCOUNT_OPEN as accOpenDate,"
				+ "account0_.CREATED_USER_ID as accDamaro,"
				+ "account0_.ACCOUNT_CLOSE as ACCOUNT_CLOSE,"
				+ "masterdata1_.APPROVED_USER as approvalUser,"
				+ "masterdata1_.APPROVED_DATE as approvalDate,"
				+ "masterdata1_.WAIT_APPROVAL_USER as lastCheckUser,"
				+ "masterdata1_.WAIT_APPROVAL_DATE as waitAppDate,"
				+ "masterdata1_.RECEIVED_USER as receivedUser,"
				+ "masterdata1_.RECEIVED_DATE as receivedDate,"
				+ "masterdata1_.TBO_USER as tboUser,"
				+ "masterdata1_.TBO_DATE as tboDate,"
				+ "masterdata1_.PROGRESS_USER as progressUser,"
				+ "masterdata1_.PROGRESS_DATE as progressDate,"
				+ "masterdata1_.SEND_EQ_DATE as sendToEQdate "
				+

				"from M_ACCOUNT account0_ left join T_CIF masterdata1_ on account0_.MD_ID=masterdata1_.__SID "
				+ "left join M_BRANCH branch2_ ON account0_.BRANCH=branch2_.BRANCH_CODE "
				+ "where account0_.MD_ID=masterdata1_.__SID and masterdata1_.BRANCH_ID=branch2_.__ID "
				+ "and branch2_.BRANCH_CODE='"
				+ branchId
				+ "' "
				+ "and CONVERT(VARCHAR(20),account0_.ACCOUNT_OPEN, 112) BETWEEN '"
				+ from + "' AND '" + to + "' "
				+ "order by branch2_.BRANCH_CODE";
		SQLQuery query = session.createSQLQuery(q);

		query.addScalar("branchCode", Hibernate.STRING)
				// .addScalar("branchName", Hibernate.STRING)
				.addScalar("account", Hibernate.STRING)
				.addScalar("cif", Hibernate.STRING)
				.addScalar("accOpenDate", Hibernate.DATE)
				.addScalar("accDamaro", Hibernate.STRING)
				.addScalar("approvalUser", Hibernate.STRING)
				.addScalar("approvalDate", Hibernate.DATE)
				.addScalar("lastCheckUser", Hibernate.STRING)
				.addScalar("waitAppDate", Hibernate.DATE)
				.addScalar("receivedUser", Hibernate.STRING)
				.addScalar("receivedDate", Hibernate.DATE)
				.addScalar("tboUser", Hibernate.STRING)
				.addScalar("tboDate", Hibernate.DATE)
				.addScalar("progressUser", Hibernate.STRING)
				.addScalar("progressDate", Hibernate.DATE)
				.addScalar("sendToEQdate", Hibernate.DATE)
				.setResultTransformer(
						Transformers.aliasToBean(Report03Model.class))
				.setCacheMode(CacheMode.GET).list();
		rows = query.list();
		System.out.println(q);
		System.out.println("list count: " + rows.size());
		return rows;
	}

	@Override
	public List<Report03Model> findByDateRangeByModel(String from, String to) {
		List<Report03Model> rows = null;
		String q = "select  branch2_.BRANCH_CODE as branchCode,"
				+
				// "branch2_.BRANCH_NAME as branchName,"+
				"account0_.ACCOUNT as account,"
				+ "masterdata1_.CIF_SID as cif,"
				+ "account0_.ACCOUNT_OPEN as accOpenDate,"
				+ "account0_.CREATED_USER_ID as accDamaro,"
				+ "account0_.ACCOUNT_CLOSE as ACCOUNT_CLOSE,"
				+ "masterdata1_.APPROVED_USER as approvalUser,"
				+ "masterdata1_.APPROVED_DATE as approvalDate,"
				+ "masterdata1_.WAIT_APPROVAL_USER as lastCheckUser,"
				+ "masterdata1_.WAIT_APPROVAL_DATE as waitAppDate,"
				+ "masterdata1_.RECEIVED_USER as receivedUser,"
				+ "masterdata1_.RECEIVED_DATE as receivedDate,"
				+ "masterdata1_.TBO_USER as tboUser,"
				+ "masterdata1_.TBO_DATE as tboDate,"
				+ "masterdata1_.PROGRESS_USER as progressUser,"
				+ "masterdata1_.PROGRESS_DATE as progressDate,"
				+ "masterdata1_.SEND_EQ_DATE as sendToEQdate "
				+

				"from M_ACCOUNT account0_ left join T_CIF masterdata1_ on account0_.MD_ID=masterdata1_.__SID "
				+ "left join M_BRANCH branch2_ ON account0_.BRANCH=branch2_.BRANCH_CODE "
				+ "where account0_.MD_ID=masterdata1_.__SID and masterdata1_.BRANCH_ID=branch2_.__ID  "
				+ "and CONVERT(VARCHAR(20),account0_.ACCOUNT_OPEN, 112) BETWEEN '"
				+ from + "' AND '" + to + "' "
				+ "order by branch2_.BRANCH_CODE";
		SQLQuery query = session.createSQLQuery(q);

		query.addScalar("branchCode", Hibernate.STRING)
				// .addScalar("branchName", Hibernate.STRING)
				.addScalar("account", Hibernate.STRING)
				.addScalar("cif", Hibernate.STRING)
				.addScalar("accOpenDate", Hibernate.DATE)
				.addScalar("accDamaro", Hibernate.STRING)
				.addScalar("approvalUser", Hibernate.STRING)
				.addScalar("approvalDate", Hibernate.DATE)
				.addScalar("lastCheckUser", Hibernate.STRING)
				.addScalar("waitAppDate", Hibernate.DATE)
				.addScalar("receivedUser", Hibernate.STRING)
				.addScalar("receivedDate", Hibernate.DATE)
				.addScalar("tboUser", Hibernate.STRING)
				.addScalar("tboDate", Hibernate.DATE)
				.addScalar("progressUser", Hibernate.STRING)
				.addScalar("progressDate", Hibernate.DATE)
				.addScalar("sendToEQdate", Hibernate.DATE)
				.setResultTransformer(
						Transformers.aliasToBean(Report03Model.class))
				.setCacheMode(CacheMode.GET).list();
		rows = query.list();
		System.out.println(q);
		System.out.println("list count: " + rows.size());
		return rows;
	}
}